--****************************************************************************
--Table: TAIKHOAN
--****************************************************************************

--select
if exists(select name from sysobjects where name='sp_Select_TaiKhoan')
drop proc sp_Select_TaiKhoan
go
create proc sp_Select_TaiKhoan
as 
begin
	select * from TAIKHOAN
end

--insert
if exists(select name from sysobjects where name='sp_Insert_TaiKhoan')
drop proc sp_Insert_TaiKhoan
go
create proc sp_Insert_TaiKhoan
	@MaTaiKhoan varchar(7),
	@PassWord nvarchar(100),
	@MaLoaiTaiKhoan int
as 
begin
	declare @flag int
	set @flag=0

	if exists (select * from LOAITAIKHOAN where MaLoaiTaiKhoan=@MaLoaiTaiKhoan)
		set @flag = 1

	begin tran
	if(exists(select * from TAIKHOAN where MaTaiKhoan=@MaTaiKhoan))
		begin
			raiserror (N'Thông tin cần thêm vào đã có trong CSDL!!!', 16,1)
			rollback tran
			return
		end

		else
		begin
			if (@flag =1)
			begin
				insert into TAIKHOAN values(@MaTaiKhoan,@Password,@MaLoaiTaiKhoan)
				commit tran
			end
			else
			begin
				raiserror (N'Thông tin cần thêm không hợp lệ !!!',1,16)
				rollback tran
				return
			end				
		end
end

--delete
if exists(select name from sysobjects where name='sp_Delete_TaiKhoan')
drop proc sp_Delete_TaiKhoan
go
create proc sp_Delete_TaiKhoan
	@MaTaiKhoan varchar(7)
as 
begin
	begin tran
	if not exists(select* from TAIKHOAN where MaTaiKhoan=@MaTaiKhoan)
	begin
		raiserror (N'Thông tin cần xoá không tồn tại!!!',16,1)
		rollback tran
		return
	end
	else --if( exists (select * from TAIKHOAN where MaTaiKhoan = @MaTaiKhoan))
		begin 
			Delete from TAIKHOAN where MaTaiKhoan = @MaTaiKhoan
			commit tran			
		end 
end

--Update
if exists(select name from sysobjects where name='sp_Update_TaiKhoan')
drop proc sp_Update_TaiKhoan
go
create proc sp_Update_TaiKhoan
	@MaTaiKhoan varchar(7),
	@PassWord nvarchar(100),
	@MaLoaiTaiKhoan int
as 
begin
	declare @flag int
	set @flag=0

	if exists (select * from LOAITAIKHOAN where MaLoaiTaiKhoan=@MaLoaiTaiKhoan)
		set @flag = 1
	
	begin tran
	if(@flag=1)
		begin
			Update TAIKHOAN set PassWord = @PassWord, MaLoaiTaiKhoan =@MaLoaiTaiKhoan
			where MaTaiKhoan = @MaTaiKhoan
			commit tran	
		end 
	else --flag=0
		begin
			raiserror (N'Dữ liệu cần cập nhật không hợp lệ !!!',1,16)
			rollback tran
			return
		end		
end


